﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace OCAP_FACAEmail_Api.Controllers
{
    public class EmailController : Controller
    {
        // GET: Email
        public ActionResult Index()
        {
            return View();
        }

        //获取
        public JsonResult GetEmail(int page = 1, int limit = 10, string prefix_dept = "", string dept = "", string dri = "")
        {
            try
            {
                SqlHelper sqlHelper = new SqlHelper();
                string baseQuery = "SELECT * FROM [dbo].[OCAP_DRI_EMail] WHERE 1=1";

                // 添加搜索条件
                if (!string.IsNullOrWhiteSpace(prefix_dept))
                {
                    baseQuery += $" AND Prefix_dept LIKE '%{prefix_dept}%'";
                }
                if (!string.IsNullOrWhiteSpace(dept))
                {
                    baseQuery += $" AND Dept LIKE '%{dept}%'";
                }
                if (!string.IsNullOrWhiteSpace(dri))
                {
                    baseQuery += $" AND DRI LIKE '%{dri}%'";
                }

                // 获取总数
                string countQuery = $"SELECT COUNT(1) FROM ({baseQuery}) AS Total";
                int total = Convert.ToInt32(sqlHelper.ExecuteScalar(countQuery));

                // 分页查询
                string query = $@"SELECT * FROM (
                    SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNum, * 
                    FROM ({baseQuery}) AS Temp
                ) AS T 
                WHERE RowNum BETWEEN {(page - 1) * limit + 1} AND {page * limit}";

                using (var dt = sqlHelper.GetData(query))
                {
                    List<EmailInfo> list = new List<EmailInfo>();
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        EmailInfo info = new EmailInfo
                        {
                            ID = dt.Rows[i]["ID"]?.ToString() ?? "",
                            Prefix_dept = dt.Rows[i]["Prefix_dept"]?.ToString() ?? "",
                            Dept = dt.Rows[i]["Dept"]?.ToString() ?? "",
                            DRI = dt.Rows[i]["DRI"]?.ToString() ?? "",
                            Email = dt.Rows[i]["Email"]?.ToString() ?? ""
                        };
                        list.Add(info);
                    }
                    return Json(new
                    {
                        code = 0,
                        msg = "操作成功",
                        count = total,
                        data = list
                    }, JsonRequestBehavior.AllowGet);
                }
            }
            catch (Exception ex)
            {
                return Json(new
                {
                    code = 500,
                    msg = "获取失败：" + ex.Message
                }, JsonRequestBehavior.AllowGet);
            }
        }

        //添加
        public JsonResult AddEmail(EmailInfo model)
        {
            try
            {
                // 验证输入
                if (string.IsNullOrWhiteSpace(model.Prefix_dept))
                {
                    return Json(new { success = false, message = "部门前缀不能为空", code = 400 });
                }

                if (string.IsNullOrWhiteSpace(model.Dept))
                {
                    return Json(new { success = false, message = "Dept不能为空", code = 400 });
                }
                if (string.IsNullOrWhiteSpace(model.DRI))
                {
                    return Json(new { success = false, message = "DRI不能为空", code = 400 });
                }
                if (string.IsNullOrWhiteSpace(model.Email))
                {
                    return Json(new { success = false, message = "Email不能为空", code = 400 });
                }
                SqlHelper sqlHelper = new SqlHelper();

                string cmd = @"INSERT INTO OCAP_DRI_EMail 
                        (Prefix_dept, Dept, DRI, Email)
                         VALUES 
                        (@Prefix_dept, @Dept, @DRI, @Email)";

                var parameters = new Dictionary<string, object>
        {
            { "@Prefix_dept", model.Prefix_dept },
            { "@Dept", model.Dept },
            { "@DRI", model.DRI },
            { "@Email", model.Email },
        };

               
                int result = sqlHelper.ExecuteParameterizedQuery(cmd, parameters);

                return Json(new
                {
                    success = result > 0,
                    message = result > 0 ? "添加成功" : "添加失败",
                    code = result > 0 ? 200 : 500
                });
            }
            catch (Exception ex)
            {
                return Json(new
                {
                    success = false,
                    message = "添加失败: " + ex.Message,
                    code = 500
                });
            }
        }

        //删除
        public JsonResult DeleteEmail(string ID)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(ID) || !long.TryParse(ID, out _))
                {
                    return Json(new { success = false, message = "无效的ID", code = 400 });
                }

                SqlHelper sqlHelper = new SqlHelper();
                string cmd = $"DELETE FROM [dbo].[OCAP_DRI_EMail] WHERE ID = {ID}";

                int result = sqlHelper.ExecuteNonQuery(cmd);

                return Json(new
                {
                    success = result > 0,
                    message = result > 0 ? "删除成功" : "删除失败",
                    code = result > 0 ? 200 : 500
                });
            }
            catch (Exception ex)
            {
                return Json(new
                {
                    success = false,
                    message = "删除失败: " + ex.Message,
                    code = 500
                });
            }
        }

        //修改
        public JsonResult UpdateEmail(EmailInfo model)
        {
            try
            {
                if (string.IsNullOrEmpty(model.ID))
                {
                    return Json(new { code = 400, msg = "角色ID不能为空" });
                }

                // 验证其他字段
                if (string.IsNullOrWhiteSpace(model.Prefix_dept))
                {
                    return Json(new { code = 400, msg = "部门前缀不能为空" });
                }
                if (string.IsNullOrWhiteSpace(model.Dept))
                {
                    return Json(new { code = 400, msg = "Dept不能为空" });
                }
                if (string.IsNullOrWhiteSpace(model.DRI))
                {
                    return Json(new { code = 400, msg = "DRI不能为空" });
                }
                if (string.IsNullOrWhiteSpace(model.Email))
                {
                    return Json(new { code = 400, msg = "Email不能为空" });
                }

                SqlHelper sqlHelper = new SqlHelper();

                
                string cmd = @"UPDATE OCAP_DRI_EMail SET 
                      Prefix_dept = @Prefix_dept,
                      Dept = @Dept,
                      DRI = @DRI,
                      Email = @Email
                      WHERE ID = @ID";

                var parameters = new Dictionary<string, object>
        {
            { "@Prefix_dept", model.Prefix_dept },
            { "@Dept", model.Dept },
            { "@DRI", model.DRI },
            { "@Email", model.Email },
            { "@ID", model.ID }
        };

                int result = sqlHelper.ExecuteParameterizedQuery(cmd, parameters);

                return Json(new
                {
                    code = result > 0 ? 0 : 500,
                    msg = result > 0 ? "更新成功" : "更新失败"
                });
            }
            catch (Exception ex)
            {
                return Json(new { code = 500, msg = "更新失败: " + ex.Message });
            }
        }




    }

    // 信息模型
    public class EmailInfo
    {
        public string ID { get; set; }
        public string Prefix_dept { get; set; }
        public string Dept { get; set; }
        public string DRI { get; set; }
        public string Email { get; set; }
    }

    
    public static class SqlHelperExtensions
    {
        public static int ExecuteParameterizedQuery(this SqlHelper sqlHelper, string commandText, Dictionary<string, object> parameters)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["MsSqlConnet_APPDB"]))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    foreach (var param in parameters)
                    {
                        cmd.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }
    }
}